const express = require('express');
const multer = require('multer');
const mysql = require('mysql2/promise');
const XLSX = require('xlsx');
const cors = require('cors');
const path = require('path');
const fs = require('fs');
const config = require('./config');

const app = express();
const port = process.env.PORT || config.server.port;

// 中间件配置
app.use(cors());
app.use(express.json());
app.use(express.static(path.join(__dirname)));

// 文件上传配置
const storage = multer.diskStorage({
    destination: function (req, file, cb) {
        const uploadDir = path.join(__dirname, config.upload.uploadDir);
        if (!fs.existsSync(uploadDir)) {
            fs.mkdirSync(uploadDir, { recursive: true });
        }
        cb(null, uploadDir);
    },
    filename: function (req, file, cb) {
        const timestamp = Date.now();
        cb(null, `excel_${timestamp}.xlsx`);
    }
});

const upload = multer({ 
    storage: storage,
    fileFilter: function (req, file, cb) {
        if (file.mimetype === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' ||
            file.mimetype === 'application/vnd.ms-excel') {
            cb(null, true);
        } else {
            cb(new Error('只允许上传Excel文件'));
        }
    },
    limits: {
        fileSize: config.upload.maxFileSize
    }
});

// MySQL数据库连接配置
const dbConfig = config.mysql;

// 创建数据库连接池
let pool;

async function initDatabase() {
    try {
        pool = mysql.createPool(dbConfig);
        
        // 测试连接
        const connection = await pool.getConnection();
        console.log('MySQL数据库连接成功');
        
        // 创建表（如果不存在）
        await createTables(connection);
        connection.release();
        
    } catch (error) {
        console.error('数据库连接失败:', error);
        process.exit(1);
    }
}

async function createTables(connection) {
    try {
        // 创建Excel数据表 - 简化版，只包含订单编号和金额
        const createTableSQL = `
            CREATE TABLE IF NOT EXISTS ${config.table.name} (
                id INT AUTO_INCREMENT PRIMARY KEY,
                order_number VARCHAR(100) NOT NULL,
                price DECIMAL(10,2),
                upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                file_name VARCHAR(200),
                INDEX idx_order_number (order_number),
                INDEX idx_upload_time (upload_time),
                UNIQUE KEY uk_order_number (order_number)
            ) ENGINE=InnoDB DEFAULT CHARSET=${config.table.charset} COLLATE=${config.table.collate}
        `;
        
        await connection.execute(createTableSQL);
        console.log('数据表创建成功或已存在');
        
    } catch (error) {
        console.error('创建表失败:', error);
        throw error;
    }
}

// 启动时初始化数据库
initDatabase();

// 健康检查端点
app.get('/health', (req, res) => {
    res.json({
        status: 'ok',
        timestamp: new Date().toISOString(),
        uptime: process.uptime(),
        memory: process.memoryUsage(),
        version: process.version
    });
});

// 批量插入数据的函数
async function batchInsertData(dataRows, orderNumberIndex, priceIndex, fileName) {
    if (dataRows.length === 0) return { successCount: 0, errorCount: 0, errors: [] };
    
    const batchSize = 1000; // 每批处理1000条记录
    let successCount = 0;
    let errorCount = 0;
    const errors = [];
    
    // 分批处理数据
    for (let i = 0; i < dataRows.length; i += batchSize) {
        const batch = dataRows.slice(i, i + batchSize);
        const values = [];
        const placeholders = [];
        
        for (let j = 0; j < batch.length; j++) {
            const row = batch[j];
            if (row.length === 0 || !row[orderNumberIndex]) continue;
            
            const orderNumber = String(row[orderNumberIndex]).trim();
            const price = parseFloat(row[priceIndex]) || 0;
            
            if (orderNumber) {
                values.push(orderNumber, price, fileName);
                placeholders.push('(?, ?, ?)');
            }
        }
        
        if (values.length > 0) {
            try {
                const insertSQL = `
                    INSERT INTO ${config.table.name} 
                    (order_number, price, file_name) 
                    VALUES ${placeholders.join(', ')}
                    ON DUPLICATE KEY UPDATE
                    price = VALUES(price),
                    upload_time = CURRENT_TIMESTAMP,
                    file_name = VALUES(file_name)
                `;
                
                await pool.execute(insertSQL, values);
                successCount += values.length / 3; // 每条记录有3个值
                
            } catch (error) {
                errorCount += batch.length;
                errors.push(`批次${Math.floor(i / batchSize) + 1}: ${error.message}`);
            }
        }
    }
    
    return { successCount: Math.floor(successCount), errorCount, errors };
}

// 计算预计剩余时间
function calculateEstimatedTime(startTime, processedCount, totalCount) {
    if (processedCount === 0) return '';
    
    const elapsed = Date.now() - startTime;
    const rate = processedCount / elapsed; // 每秒处理记录数
    const remaining = totalCount - processedCount;
    const estimatedSeconds = remaining / rate;
    
    if (estimatedSeconds < 60) {
        return `${Math.ceil(estimatedSeconds)}秒`;
    } else if (estimatedSeconds < 3600) {
        return `${Math.ceil(estimatedSeconds / 60)}分钟`;
    } else {
        return `${Math.ceil(estimatedSeconds / 3600)}小时`;
    }
}

// 路由：Excel文件上传（优化版 - 批量插入）
app.post('/api/upload-excel-batch', upload.single('excel'), async (req, res) => {
    const startTime = Date.now();
    
    try {
        if (!req.file) {
            return res.status(400).json({
                payment: -2,
                message: '没有选择文件'
            });
        }

        const filePath = req.file.path;
        console.log('上传的文件路径:', filePath);

        // 读取Excel文件
        const workbook = XLSX.readFile(filePath);
        const sheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[sheetName];
        
        // 转换为JSON数据
        const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
        
        if (jsonData.length < 2) {
            return res.status(400).json({
                payment: -2,
                message: 'Excel文件内容为空或格式不正确'
            });
        }

        // 获取表头
        const headers = jsonData[0];
        console.log('Excel表头:', headers);

        // 查找关键列索引 - 简化版，只识别订单编号和金额
        const orderNumberIndex = headers.findIndex(h => 
            h && (h.toString().includes('订单') || h.toString().includes('订单号') || h.toString().includes('编号') || h.toString().includes('order'))
        );
        const priceIndex = headers.findIndex(h => 
            h && (h.toString().includes('价格') || h.toString().includes('金额') || h.toString().includes('price') || h.toString().includes('amount'))
        );

        // 验证必要列是否存在
        if (orderNumberIndex === -1) {
            return res.json({
                payment: -2,
                message: 'Excel文件中未找到订单编号列，请检查文件格式'
            });
        }
        
        if (priceIndex === -1) {
            return res.json({
                payment: -2,
                message: 'Excel文件中未找到金额列，请检查文件格式'
            });
        }

        // 处理数据行
        const dataRows = jsonData.slice(1);
        console.log(`开始处理 ${dataRows.length} 条数据...`);
        
        // 使用批量插入
        const result = await batchInsertData(dataRows, orderNumberIndex, priceIndex, req.file.filename);
        
        // 删除临时文件
        fs.unlinkSync(filePath);
        
        const processingTime = Date.now() - startTime;
        console.log(`处理完成，耗时: ${processingTime}ms，成功: ${result.successCount}，失败: ${result.errorCount}`);

        res.json({
            payment: result.successCount > 0 ? result.successCount : -1,
            message: `成功处理 ${result.successCount} 条数据${result.errorCount > 0 ? `，失败 ${result.errorCount} 条` : ''}，总耗时 ${Math.round(processingTime / 1000)}秒`,
            total: dataRows.length,
            success: result.successCount,
            error: result.errorCount,
            errors: result.errors.slice(0, 5), // 只返回前5个错误
            processingTime: processingTime
        });

    } catch (error) {
        console.error('处理Excel文件失败:', error);
        res.json({
            payment: -2,
            message: '处理文件失败: ' + error.message
        });
    }
});

// 路由：Excel文件上传（保持兼容性）
app.post('/api/upload-excel', upload.single('excel'), async (req, res) => {
    try {
        if (!req.file) {
            return res.status(400).json({
                payment: -2,
                message: '没有选择文件'
            });
        }

        const filePath = req.file.path;
        console.log('上传的文件路径:', filePath);

        // 读取Excel文件
        const workbook = XLSX.readFile(filePath);
        const sheetName = workbook.SheetNames[0];
        const worksheet = workbook.Sheets[sheetName];
        
        // 转换为JSON数据
        const jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
        
        if (jsonData.length < 2) {
            return res.status(400).json({
                payment: -2,
                message: 'Excel文件内容为空或格式不正确'
            });
        }

        // 获取表头
        const headers = jsonData[0];
        console.log('Excel表头:', headers);

        // 查找关键列索引 - 简化版，只识别订单编号和金额
        const orderNumberIndex = headers.findIndex(h => 
            h && (h.toString().includes('订单') || h.toString().includes('订单号') || h.toString().includes('编号') || h.toString().includes('order'))
        );
        const priceIndex = headers.findIndex(h => 
            h && (h.toString().includes('价格') || h.toString().includes('金额') || h.toString().includes('price') || h.toString().includes('amount'))
        );

        // 验证必要列是否存在
        if (orderNumberIndex === -1) {
            return res.json({
                payment: -2,
                message: 'Excel文件中未找到订单编号列，请检查文件格式'
            });
        }
        
        if (priceIndex === -1) {
            return res.json({
                payment: -2,
                message: 'Excel文件中未找到金额列，请检查文件格式'
            });
        }

        // 处理数据行
        const dataRows = jsonData.slice(1);
        let successCount = 0;
        let errorCount = 0;
        const errors = [];

        for (let i = 0; i < dataRows.length; i++) {
            const row = dataRows[i];
            if (row.length === 0 || !row[orderNumberIndex]) continue;

            try {
                const orderNumber = String(row[orderNumberIndex]).trim();
                const price = parseFloat(row[priceIndex]) || 0;

                // 插入数据库 - 简化版，只保存订单编号和金额
                const insertSQL = `
                    INSERT INTO ${config.table.name} 
                    (order_number, price, file_name) 
                    VALUES (?, ?, ?)
                    ON DUPLICATE KEY UPDATE
                    price = VALUES(price),
                    upload_time = CURRENT_TIMESTAMP,
                    file_name = VALUES(file_name)
                `;

                await pool.execute(insertSQL, [
                    orderNumber, price, req.file.filename
                ]);

                successCount++;
            } catch (error) {
                errorCount++;
                errors.push(`第${i + 2}行: ${error.message}`);
            }
        }

        // 删除临时文件
        fs.unlinkSync(filePath);

        res.json({
            payment: successCount > 0 ? successCount : -1,
            message: `成功处理 ${successCount} 条数据${errorCount > 0 ? `，失败 ${errorCount} 条` : ''}`,
            total: dataRows.length,
            success: successCount,
            error: errorCount,
            errors: errors.slice(0, 5) // 只返回前5个错误
        });

    } catch (error) {
        console.error('处理Excel文件失败:', error);
        res.json({
            payment: -2,
            message: '处理文件失败: ' + error.message
        });
    }
});

// 路由：根据订单号查询 - 采用 :shop-:id 格式
app.get('/all/:shop-:id', async (req, res) => {
    try {
        const orderNumber = req.params.id.trim();
        const shop = req.params.shop; // shop参数忽略，但保留兼容性
        
        if (!orderNumber) {
            return res.json({ payment: -2 });
        }

        const querySQL = `
            SELECT order_number, price, upload_time
            FROM ${config.table.name} 
            WHERE order_number = ?
            ORDER BY upload_time DESC 
            LIMIT 1
        `;

        const [rows] = await pool.execute(querySQL, [orderNumber]);

        if (rows.length === 0) {
            return res.json({ payment: -1 });
        }

        const order = rows[0];
        res.json({
            payment: order.price || 0,
            order_number: order.order_number,
            upload_time: order.upload_time
        });

    } catch (error) {
        console.error('查询订单失败:', error);
        res.json({ payment: -2 });
    }
});

// 兼容性接口：保持原有的 /:orderNumber 格式
app.get('/api/search-order/:orderNumber', async (req, res) => {
    try {
        const orderNumber = req.params.orderNumber.trim();
        
        if (!orderNumber) {
            return res.json({ payment: -2 });
        }
        
        const querySQL = `
            SELECT order_number, price, upload_time
            FROM ${config.table.name} 
            WHERE order_number = ?
            ORDER BY upload_time DESC 
            LIMIT 1
        `;

        const [rows] = await pool.execute(querySQL, [orderNumber]);

        if (rows.length === 0) {
            return res.json({ payment: -1 });
        }

        const order = rows[0];
        res.json({
            payment: order.price || 0,
            order_number: order.order_number,
            upload_time: order.upload_time
        });

    } catch (error) {
        console.error('查询订单失败:', error);
        res.json({ payment: -2 });
    }
});

// 错误处理中间件
app.use((error, req, res, next) => {
    console.error('服务器错误:', error);
    res.json({
        payment: -2,
        message: '服务器内部错误: ' + error.message
    });
});

// 启动服务器
app.listen(port, () => {
    console.log(`Excel上传服务器运行在端口 ${port}`);
    console.log(`访问地址: http://localhost:${port}/upload.html`);
    console.log('新增批量上传API: /api/upload-excel-batch');
});

// 优雅关闭
process.on('SIGINT', async () => {
    console.log('\n正在关闭服务器...');
    if (pool) {
        await pool.end();
        console.log('数据库连接已关闭');
    }
    process.exit(0);
}); 